'use strict';

const Service = require('egg').Service;

class IndexService extends Service {
    /**
     * 
     * 查找项目列表
     * @param {any} id user id
     * @returns 
     * @memberof IndexService
     */
    async project(id) {
        let result = await this.app.mysql.query(`SELECT
        tsys.*,
        us.user_account,
        temp.count 
    FROM
        tab_system tsys
        LEFT JOIN (
    SELECT
        count( api_id ) AS count,
        system_id AS id 
    FROM
        tab_api 
    GROUP BY
        system_id 
        ) AS temp ON temp.id = tsys.system_id 
         LEFT JOIN  tab_users AS us ON tsys.user_id=us.user_id
    ORDER BY
        tsys.system_time DESC`)
        return result
    }

    async projectInfoModel(){
        let result=await this.app.mysql.query(`
        SELECT 
            ts.*,
            tu.user_account,
            tu.user_type
        FROM 
            tab_system AS ts

        LEFT JOIN 
            tab_users AS tu ON ts.user_id=tu.user_id
        `)

        return result;
    }
}

module.exports = IndexService;